"""
权限管理相关的数据库操作
"""
from typing import List, Optional

from app.models.system_models import Permission, PermissionType
from app.schemas.system_schemas import PermissionCreate, PermissionUpdate, PermissionTreeResponse
from common.database import DatabaseService
from common.exceptions import BizException, ErrorCode


async def get_permission_by_id(db: DatabaseService, permission_id: int) -> Optional[Permission]:
	"""根据权限ID获取权限信息"""
	cursor = None
	try:
		cursor = db.get_cursor()
		sql = """
              SELECT id,
                     perm_code,
                     perm_name,
                     type,
                     parent_id,
                     path,
                     level,
                     sort,
                     deleted,
                     gmt_create
              FROM base_permission
              WHERE id = %s
                AND deleted = 0 \
			  """
		cursor.execute(sql, (permission_id,))
		row = cursor.fetchone()
		if row:
			# 确保id字段是整数类型
			row['id'] = int(row['id'])
			return Permission(**row)
		return None
	except Exception as e:
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"查询权限失败: {str(e)}")
	finally:
		if cursor:
			cursor.close()


async def get_permission_by_code(db: DatabaseService, perm_code: str) -> Optional[Permission]:
	"""根据权限编码获取权限信息"""
	cursor = None
	try:
		cursor = db.get_cursor()
		sql = """
              SELECT id,
                     perm_code,
                     perm_name,
                     type,
                     parent_id,
                     path,
                     level,
                     sort,
                     deleted,
                     gmt_create
              FROM base_permission
              WHERE perm_code = %s
                AND deleted = 0 \
			  """
		cursor.execute(sql, (perm_code,))
		row = cursor.fetchone()
		if row:
			# 确保id字段是整数类型
			row['id'] = int(row['id'])
			return Permission(**row)
		return None
	except Exception as e:
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"查询权限失败: {str(e)}")
	finally:
		if cursor:
			cursor.close()


async def create_permission(db: DatabaseService, permission_data: PermissionCreate) -> int:
	"""创建新权限"""
	cursor = None
	try:
		# 检查权限编码是否已存在
		existing_permission = await get_permission_by_code(db, permission_data.perm_code)
		if existing_permission:
			raise BizException.from_error_code(
				ErrorCode.CONFLICT,
				message=f"权限编码 '{permission_data.perm_code}' 已存在"
			)

		# 如果有父权限，验证父权限是否存在
		if permission_data.parent_id > 0:
			parent_permission = await get_permission_by_id(db, permission_data.parent_id)
			if not parent_permission:
				raise BizException.from_error_code(
					ErrorCode.NOT_FOUND,
					message="父权限不存在"
				)

		cursor = db.get_cursor()
		sql = """
              INSERT INTO base_permission (perm_code, perm_name, type, parent_id, path, level, sort, deleted,
                                           gmt_create)
              VALUES (%s, %s, %s, %s, %s, %s, %s, 0, NOW()) \
			  """
		cursor.execute(sql, (
			permission_data.perm_code,
			permission_data.perm_name,
			permission_data.type.value,
			permission_data.parent_id,
			permission_data.path,
			permission_data.level,
			permission_data.sort
		))
		db.conn.commit()
		return cursor.lastrowid
	except BizException:
		db.conn.rollback()
		raise
	except Exception as e:
		db.conn.rollback()
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"创建权限失败: {str(e)}")
	finally:
		if cursor:
			cursor.close()


async def update_permission(db: DatabaseService, permission_id: int, permission_data: PermissionUpdate) -> bool:
	"""更新权限信息"""
	cursor = None
	try:
		# 检查权限是否存在
		existing_permission = await get_permission_by_id(db, permission_id)
		if not existing_permission:
			raise BizException.from_error_code(ErrorCode.NOT_FOUND, message="权限不存在")

		# 构建更新SQL
		update_fields = []
		params = []

		if permission_data.perm_name is not None:
			update_fields.append("perm_name = %s")
			params.append(permission_data.perm_name)

		if permission_data.type is not None:
			update_fields.append("type = %s")
			params.append(permission_data.type.value)

		if permission_data.parent_id is not None:
			# 验证父权限是否存在
			if permission_data.parent_id > 0:
				parent_permission = await get_permission_by_id(db, permission_data.parent_id)
				if not parent_permission:
					raise BizException.from_error_code(
						ErrorCode.NOT_FOUND,
						message="父权限不存在"
					)
			update_fields.append("parent_id = %s")
			params.append(permission_data.parent_id)

		if permission_data.path is not None:
			update_fields.append("path = %s")
			params.append(permission_data.path)

		if permission_data.level is not None:
			update_fields.append("level = %s")
			params.append(permission_data.level)

		if permission_data.sort is not None:
			update_fields.append("sort = %s")
			params.append(permission_data.sort)

		if not update_fields:
			return True  # 没有需要更新的字段

		params.append(permission_id)

		cursor = db.get_cursor()
		sql = f"UPDATE base_permission SET {', '.join(update_fields)} WHERE id = %s AND deleted = 0"
		cursor.execute(sql, params)
		db.conn.commit()

		return cursor.rowcount > 0
	except BizException:
		db.conn.rollback()
		raise
	except Exception as e:
		db.conn.rollback()
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"更新权限失败: {str(e)}")
	finally:
		if cursor:
			cursor.close()


async def delete_permission(db: DatabaseService, permission_id: int) -> bool:
	"""删除权限（逻辑删除）"""
	cursor = None
	try:
		# 检查权限是否存在
		existing_permission = await get_permission_by_id(db, permission_id)
		if not existing_permission:
			raise BizException.from_error_code(ErrorCode.NOT_FOUND, message="权限不存在")

		cursor = db.get_cursor()

		# 检查是否有子权限
		check_children_sql = "SELECT COUNT(*) FROM base_permission WHERE parent_id = %s AND deleted = 0"
		cursor.execute(check_children_sql, (permission_id,))
		result = cursor.fetchone()
		children_count = result.get('COUNT(*)') if result else 0

		if children_count > 0:
			raise BizException.from_error_code(
				ErrorCode.CONFLICT,
				message=f"该权限有 {children_count} 个子权限，无法删除"
			)

		# 检查是否有角色关联到该权限
		check_roles_sql = "SELECT COUNT(*) FROM base_role_permission WHERE perm_id = %s"
		cursor.execute(check_roles_sql, (permission_id,))
		result = cursor.fetchone()
		role_count = result.get('COUNT(*)') if result else 0

		if role_count > 0:
			raise BizException.from_error_code(
				ErrorCode.CONFLICT,
				message=f"该权限已分配给 {role_count} 个角色，无法删除"
			)

		# 执行逻辑删除
		sql = "UPDATE base_permission SET deleted = 1 WHERE id = %s AND deleted = 0"
		cursor.execute(sql, (permission_id,))
		db.conn.commit()

		return cursor.rowcount > 0
	except BizException:
		db.conn.rollback()
		raise
	except Exception as e:
		db.conn.rollback()
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"删除权限失败: {str(e)}")
	finally:
		if cursor:
			cursor.close()


async def get_all_permissions(db: DatabaseService) -> List[Permission]:
	"""获取所有权限列表"""
	cursor = None
	try:
		cursor = db.get_cursor()
		sql = """
              SELECT id,
                     perm_code,
                     perm_name,
                     type,
                     parent_id,
                     path,
                     level,
                     sort,
                     deleted,
                     gmt_create
              FROM base_permission
              WHERE deleted = 0
              ORDER BY level, sort, id \
			  """
		cursor.execute(sql)
		rows = cursor.fetchall()

		permissions = []
		for row in rows:
			# 确保id字段是整数类型
			row['id'] = int(row['id'])
			permissions.append(Permission(**row))

		return permissions
	except Exception as e:
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"查询权限列表失败: {str(e)}")
	finally:
		if cursor:
			cursor.close()


async def get_permissions_tree(db: DatabaseService) -> List[PermissionTreeResponse]:
	"""获取权限树形结构"""
	try:
		# 获取所有权限
		all_permissions = await get_all_permissions(db)

		# 转换为字典以便查找
		permission_dict = {perm.id: PermissionTreeResponse(**perm.model_dump()) for perm in all_permissions}

		# 构建树形结构
		tree = []
		for permission in all_permissions:
			perm_tree = permission_dict[permission.id]
			if permission.parent_id == 0:
				# 根节点
				tree.append(perm_tree)
			else:
				# 子节点
				parent = permission_dict.get(permission.parent_id)
				if parent:
					parent.children.append(perm_tree)

		# 排序
		def sort_tree(nodes):
			nodes.sort(key=lambda x: (x.sort, x.id))
			for node in nodes:
				if node.children:
					sort_tree(node.children)

		sort_tree(tree)
		return tree
	except Exception as e:
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"构建权限树失败: {str(e)}")


async def get_permissions_by_type(db: DatabaseService, permission_type: PermissionType) -> List[Permission]:
	"""根据权限类型获取权限列表"""
	cursor = None
	try:
		cursor = db.get_cursor()
		sql = """
              SELECT id,
                     perm_code,
                     perm_name,
                     type,
                     parent_id,
                     path,
                     level,
                     sort,
                     deleted,
                     gmt_create
              FROM base_permission
              WHERE type = %s
                AND deleted = 0
              ORDER BY level, sort, id \
			  """
		cursor.execute(sql, (permission_type.value,))
		rows = cursor.fetchall()

		permissions = []
		for row in rows:
			# 确保id字段是整数类型
			row['id'] = int(row['id'])
			permissions.append(Permission(**row))

		return permissions
	except Exception as e:
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"查询权限列表失败: {str(e)}")
	finally:
		if cursor:
			cursor.close()


async def get_user_permissions(db: DatabaseService, user_code: str) -> List[Permission]:
	"""获取用户的所有权限（通过角色关联）"""
	cursor = None
	try:
		cursor = db.get_cursor()
		sql = """
              SELECT DISTINCT p.id,
                              p.perm_code,
                              p.perm_name,
                              p.type,
                              p.parent_id,
                              p.path,
                              p.level,
                              p.sort,
                              p.deleted,
                              p.gmt_create
              FROM base_permission p
                       INNER JOIN base_role_permission rp ON p.id = rp.perm_id
                       INNER JOIN base_user_role ur ON rp.role_id = ur.role_id
                       INNER JOIN base_role r ON ur.role_id = r.id
              WHERE ur.user_code = %s
                AND p.deleted = 0
                AND r.deleted = 0
                AND r.enabled = 1
              ORDER BY p.level, p.sort, p.id \
			  """
		cursor.execute(sql, (user_code,))
		rows = cursor.fetchall()

		permissions = []
		for row in rows:
			# 确保id字段是整数类型
			row['id'] = int(row['id'])
			permissions.append(Permission(**row))

		return permissions
	except Exception as e:
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"查询用户权限失败: {str(e)}")
	finally:
		if cursor:
			cursor.close()


async def get_permission_list(db: DatabaseService, request) -> tuple[int, List[Permission]]:
	"""获取权限列表（支持分页和模糊查询）"""
	cursor = None
	try:
		cursor = db.get_cursor()
		
		# 构建查询条件
		where_conditions = ["deleted = 0"]
		params = []
		
		if request.perm_name:
			where_conditions.append("perm_name LIKE %s")
			params.append(f"%{request.perm_name}%")
			
		if request.perm_code:
			where_conditions.append("perm_code LIKE %s")
			params.append(f"%{request.perm_code}%")
			
		if request.type:
			where_conditions.append("type = %s")
			params.append(request.type.value)
		
		where_clause = " AND ".join(where_conditions)
		
		# 查询总数
		count_sql = f"SELECT COUNT(*) FROM base_permission WHERE {where_clause}"
		cursor.execute(count_sql, params)
		result = cursor.fetchone()
		total = result.get('COUNT(*)') if result else 0
		
		# 查询数据
		offset = (request.page - 1) * request.size
		data_sql = f"""
			SELECT id, perm_code, perm_name, type, parent_id, path, level, sort, deleted, gmt_create
			FROM base_permission 
			WHERE {where_clause}
			ORDER BY level, sort, id
			LIMIT %s OFFSET %s
		"""
		params.extend([request.size, offset])
		cursor.execute(data_sql, params)
		rows = cursor.fetchall()

		permissions = []
		for row in rows:
			# 确保id字段是整数类型
			row['id'] = int(row['id'])
			permissions.append(Permission(**row))

		return total, permissions
	except Exception as e:
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"查询权限列表失败: {str(e)}")
	finally:
		if cursor:
			cursor.close()


async def check_user_permission(db: DatabaseService, user_code: str, perm_code: str) -> bool:
	"""检查用户是否有指定权限"""
	cursor = None
	try:
		cursor = db.get_cursor()
		sql = """
              SELECT COUNT(*)
              FROM base_permission p
                       INNER JOIN base_role_permission rp ON p.id = rp.perm_id
                       INNER JOIN base_user_role ur ON rp.role_id = ur.role_id
                       INNER JOIN base_role r ON ur.role_id = r.id
              WHERE ur.user_code = %s
                AND p.perm_code = %s
                AND p.deleted = 0
                AND r.deleted = 0
                AND r.enabled = 1 \
			  """
		cursor.execute(sql, (user_code, perm_code))
		result = cursor.fetchone()
		# 检查结果是否为None，避免TypeError
		if result is None:
			return False
		count = result.get('COUNT(*)')
		return count > 0
	except Exception as e:
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"检查权限失败: {str(e)}")
	finally:
		if cursor:
			cursor.close()


async def initialize_work_order_permissions(db: DatabaseService) -> None:
	"""初始化工单管理权限"""
	try:
		# 检查是否已经存在工单管理权限
		existing_permission = await get_permission_by_code(db, "work_order:manage")
		if existing_permission:
			return  # 已经初始化过，直接返回

		# 创建工单管理根权限
		work_order_manage = PermissionCreate(
			perm_code="work_order:manage",
			perm_name="工单管理",
			type=PermissionType.MENU,
			parent_id=0,
			path="/work-order",
			level=1,
			sort=6
		)
		work_order_manage_id = await create_permission(db, work_order_manage)

		# 创建工单查看权限
		work_order_view = PermissionCreate(
			perm_code="work_order:view",
			perm_name="工单查看",
			type=PermissionType.API,
			parent_id=work_order_manage_id,
			path="/work-order/view",
			level=2,
			sort=1
		)
		await create_permission(db, work_order_view)

		# 创建工单创建权限
		work_order_create = PermissionCreate(
			perm_code="work_order:create",
			perm_name="工单创建",
			type=PermissionType.API,
			parent_id=work_order_manage_id,
			path="/work-order/create",
			level=2,
			sort=2
		)
		await create_permission(db, work_order_create)

		# 创建工单编辑权限
		work_order_edit = PermissionCreate(
			perm_code="work_order:edit",
			perm_name="工单编辑",
			type=PermissionType.API,
			parent_id=work_order_manage_id,
			path="/work-order/edit",
			level=2,
			sort=3
		)
		await create_permission(db, work_order_edit)

		# 创建工单删除权限
		work_order_delete = PermissionCreate(
			perm_code="work_order:delete",
			perm_name="工单删除",
			type=PermissionType.API,
			parent_id=work_order_manage_id,
			path="/work-order/delete",
			level=2,
			sort=4
		)
		await create_permission(db, work_order_delete)

		# 创建工单分派权限
		work_order_dispatch = PermissionCreate(
			perm_code="work_order:dispatch",
			perm_name="工单分派",
			type=PermissionType.API,
			parent_id=work_order_manage_id,
			path="/work-order/dispatch",
			level=2,
			sort=5
		)
		await create_permission(db, work_order_dispatch)

		# 创建工单处理权限
		work_order_process = PermissionCreate(
			perm_code="work_order:process",
			perm_name="工单处理",
			type=PermissionType.API,
			parent_id=work_order_manage_id,
			path="/work-order/process",
			level=2,
			sort=6
		)
		await create_permission(db, work_order_process)

		# 创建工单解决权限
		work_order_resolve = PermissionCreate(
			perm_code="work_order:resolve",
			perm_name="工单解决",
			type=PermissionType.API,
			parent_id=work_order_manage_id,
			path="/work-order/resolve",
			level=2,
			sort=7
		)
		await create_permission(db, work_order_resolve)

		# 创建工单关闭权限
		work_order_close = PermissionCreate(
			perm_code="work_order:close",
			perm_name="工单关闭",
			type=PermissionType.API,
			parent_id=work_order_manage_id,
			path="/work-order/close",
			level=2,
			sort=8
		)
		await create_permission(db, work_order_close)

	except Exception as e:
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"初始化工单权限失败: {str(e)}")
